package com.poidemo;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import java.io.*;
import java.text.DecimalFormat;
import java.text.MessageFormat;
import java.util.*;
import java.util.concurrent.ExecutorService;

import static java.util.concurrent.Executors.newFixedThreadPool;

/**
 * @author zhuhaibo
 * @version 1.0
 * @date 2021/3/13 21:08
 */

@Slf4j
@Data
public class ExcelUtil<T> implements IExcelOperate {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    private ExcelModel excelModel;
    private Class tClass;
    private Cell[] titleCell;
    private Workbook workBook;
    private Sheet sheet;

    public int getRows() {
        if (this.sheet != null) {
            return this.sheet.getPhysicalNumberOfRows();
        }
        return 0;
    }

    public int getFileCount() {
        if (this.sheet != null) {
            int physicalNumberOfRows = this.sheet.getPhysicalNumberOfRows();
            if (this.excelModel.isRemoveHeader()) {
                physicalNumberOfRows -= 1;
            }
            return physicalNumberOfRows / this.excelModel.getFileSize() + 1;
        }
        return 0;
    }

    @Override
    public void load(String fileName) {
        try {
            File file = new File(fileName);
            System.out.println("开始加载excel文件...");
            workBook = getWorkbook(file);
            System.out.println("Excel加载完成...");
            excelModel = new ExcelModel(workBook.getSheetAt(0).getSheetName(), true, false);
            this.excelModel.setFiles(new ArrayList<>());
            //默认读取文件的路径
            String[] ary = file.getName().split("\\.");
            String suffix = ary[1];
            this.excelModel.getFiles().add(new FileInfoModel(file.getName(), fileName, "." + suffix, file.getParent()));

            // 设置文件分割输出目录
            this.excelModel.setExcelChildFileDir(file.getParent());
            this.excelModel.setExcelFilePath(file.getParent() + File.separator + "out");

            // 判断是否去重
            if (this.excelModel.isClearRepeat()) {
                this.clearRepeat(file);
                workBook = getWorkbook(file);
            }

            if (workBook == null) {
                throw new Exception("读取excel文件出错!");
            }
            sheet = "".equals(this.excelModel.getSheetName().trim()) ? workBook.getSheetAt(0) : workBook.getSheet(this.excelModel.getSheetName());
            if (sheet == null) {
                sheet = workBook.getSheet(excelModel.getSheetName());
            }
            if (sheet == null) {
                throw new Exception("读取sheet出错!");
            }

            this.excelModel.setFileCount(getFileCount());

            if (this.excelModel.isRemoveHeader()) {
                this.excelModel.setTotalRows(sheet.getPhysicalNumberOfRows() - 1);
            } else {
                this.excelModel.setTotalRows(sheet.getPhysicalNumberOfRows());
            }
            // 用于存储列标题
            titleCell = new Cell[sheet.getRow(0).getPhysicalNumberOfCells()];

            // 将列标题存储存到一个一维数组中
            for (int i = 0; i < titleCell.length; i++) {
                titleCell[i] = sheet.getRow(0).getCell(i);
            }
        } catch (IOException e) {
            System.out.println("文件不存在: " + fileName);
            close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void splitExcel() {
        //根据文件数分割 excel，重置文件集合
        this.excelModel.setFiles(new ArrayList<>());
        //生成Guid作为文件前缀
        UUID uuid = UUID.randomUUID();
        new FileInfoModel();
        FileInfoModel fileInfoModel;
        for (int i = 1; i <= this.excelModel.getFileCount(); i++) {
            fileInfoModel = getFileInfoModelBySplit(i, uuid);
            this.excelModel.getFiles().add(this.split(fileInfoModel));
        }
    }

    @Override
    public void splitExcelThread() {
        //根据文件数分割 excel，重置文件集合
        this.excelModel.setFiles(new ArrayList<>());
        //生成Guid作为文件前缀
        UUID uuid = UUID.randomUUID();
        //根据文件数分割 excel，重置文件集合
        this.excelModel.setFiles(new ArrayList<>());
        //工作线程
        ExecutorService executorService = newFixedThreadPool(this.excelModel.getFileCount());
        new FileInfoModel();
        FileInfoModel fileInfoModel;
        for (int i = 1; i <= this.excelModel.getFileCount(); i++) {
            fileInfoModel = getFileInfoModelBySplit(i, uuid);
            ExcelThread thread = new ExcelThread(fileInfoModel);
            executorService.execute(thread);
            this.excelModel.getFiles().add(thread.getReturnFile());
        }
        executorService.shutdown();
        while (!executorService.isTerminated()) {
            //检查所有线程都执行完成
//            System.out.println("检查所有线程都执行完成");
        }
    }

    /**
     * 分割excel时获取文件信息
     *
     * @param i
     * @return
     */
    private FileInfoModel getFileInfoModelBySplit(int i, UUID uuid) {

        /*结束行*/
        int endRow = i * this.excelModel.getFileSize();
        /*起始行*/
        int beginRow = (endRow - this.excelModel.getFileSize()) + 1;
        /*如果结束行超出总记录数，结束行就等于总记录数*/
        if (endRow >= this.excelModel.getTotalRows()) {
            endRow = this.excelModel.getTotalRows();
        }
        // 自定义输出文件名
        StringBuilder fileName = new StringBuilder();
        fileName.append(this.excelModel.getSheetName()).append(beginRow).append("_").append(endRow);
        //获取文件路径
        String filePath = MessageFormat.format("{0}{1}{2}_{3}.{4}", this.excelModel.getExcelChildFileDir(), File.separator, fileName, i, EXCEL_XLSX);
        FileInfoModel fileInfoModel = new FileInfoModel();
        fileInfoModel.setFilePath(filePath);
        fileInfoModel.setBeginRow(beginRow);
        fileInfoModel.setEndRow(endRow);
        fileInfoModel.setForIndex(i - 1);
        return fileInfoModel;
    }

    /**
     * 分割excel
     *
     * @param fileInfoModel 文件实体
     * @return 分割后文件实体
     */
    private FileInfoModel split(FileInfoModel fileInfoModel) {
        File file = new File(fileInfoModel.getFilePath());
        try {
            Workbook ww = createOrGetWorkbook(file.getAbsolutePath());
            Sheet sheet = ww.getSheet(this.excelModel.getSheetName());
            if (Objects.isNull(sheet)) {
                sheet = ww.createSheet(this.excelModel.getSheetName());
            }
            // 设置表头
            setSheetHeader(ww, sheet);
            // 添加数据到excel中
            int rowIndex = 1;
            for (int iRow = fileInfoModel.getBeginRow(); iRow <= fileInfoModel.getEndRow(); iRow++, rowIndex++) {
                // 创建分割表中的行
                Row row = createRow(sheet, rowIndex);
                // 获取总表中的单元格信息
                Iterator cells = this.sheet.getRow(iRow).cellIterator();
                int cellIndex = 0;
                while (cells.hasNext()) {
                    Cell cell = (Cell) cells.next();
                    Cell emptyCell = createCell(row, cellIndex);
                    // 给分割表中的单元格赋值
                    // todo 判断单元格类型
                    emptyCell.setCellValue(getCellValue(cell));
                    // 单元格的索引需要计算
                    cellIndex++;
                }
            }
            OutputStream output = new FileOutputStream(file);
            ww.write(output);
            ww.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        String[] ary = file.getName().split("\\.");
        String fileName = ary[0];
        String suffix = ary[1];

        fileInfoModel.setFileFullName(file.getName());
        fileInfoModel.setFileName(fileName);
        fileInfoModel.setFileSuffix("." + suffix);
        return fileInfoModel;
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @param file wj
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbook(File file) throws IOException, InvalidFormatException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith(EXCEL_XLS)) {
            // Excel 2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {
            // Excel 2007/2010
            XSSFWorkbook workbook = new XSSFWorkbook(OPCPackage.open(file));
            wb = new SXSSFWorkbook(workbook);
        }
        return wb;

    }

    /**
     * 判断Excel的版本,创建一个不存在的excel文件
     *
     * @param fileName wj
     * @return Workbook
     * @throws IOException
     */
    public static Workbook createWorkbookIfNotExist(String fileName) throws IOException {
        Workbook wb = null;
        if (fileName.endsWith(EXCEL_XLS)) {
            // Excel 2003
            wb = new HSSFWorkbook();
        } else if (fileName.endsWith(EXCEL_XLSX)) {
            // Excel 2007/2010
            wb = new XSSFWorkbook();
        } else {
            try {
                throw new Exception("文件类型错误！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        try {
            OutputStream output = new FileOutputStream(fileName);
            if (wb != null) {
                wb.write(output);
            }
        } catch (FileNotFoundException e) {
            System.out.println("文件创建失败，失败原因为：" + e.getMessage());
            throw new FileNotFoundException();
        }
        System.out.println(fileName + "文件创建成功！");

        return wb;
    }

    /**
     * 创建一个新的或者已存在的Excel文档的Workbook
     *
     * @param fileName file
     * @return 工作薄
     * @throws Exception
     */
    public static Workbook createOrGetWorkbook(String fileName) throws Exception {
        InputStream input = null;
        Workbook wb = null;

        try {
            input = new FileInputStream(fileName);
            wb = WorkbookFactory.create(input);
        } catch (FileNotFoundException e) {
            System.out.println("要打开的文件不存在，正试图创建该文件，请稍后……！");
            wb = createWorkbookIfNotExist(fileName);
        } catch (OldExcelFormatException e) {
            System.out.println("文件打开失败，原因：要打开的Excel文件版本过低！");
            throw new OldExcelFormatException("文件版本过低");
        } finally {
            if (input != null) {
                input.close();
            }
        }

        return wb;
    }

    @Override
    public void close() {
        try {
            this.workBook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置表头
     *
     * @param workbook 工作薄
     * @param sheet    表
     */
    public void setSheetHeader(Workbook workbook, Sheet sheet) {
        // 设置列宽
        sheet.setDefaultColumnWidth(13);
        // 设置单元格格式
        CellStyle cellStyle = workbook.createCellStyle();
        // 边框颜色白色
        cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex());
        // 边框线型
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

        // 由于采用无边框样式以后，最后一个单元格的右边框会消失，所以给最后一个单元格的下一个单元格设置样式
        CellStyle leftCellStyle = workbook.createCellStyle();
        leftCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
        leftCellStyle.setBorderLeft(BorderStyle.THIN);
        // 内容居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置字体
        Font font = workbook.createFont();
        // 粗体
        font.setBold(true);
        // 设置字体大小
        font.setFontHeightInPoints((short) 14);
        cellStyle.setFont(font);

        // 添加表头
        Row emptyRow = createRow(sheet, 0);
        // 获取总表头
        Row row = this.sheet.getRow(0);
        for (int iColumn = 0; iColumn < this.titleCell.length; iColumn++) {
            Cell cell = row.getCell(iColumn);
            // 创建单元格对象
            Cell emptyCell = createCell(emptyRow, iColumn);

            // 设置单元格样式
            emptyCell.setCellStyle(cellStyle);

            // 判断单元格类型,分割表表头单元格赋值
            emptyCell.setCellValue(getCellValue(cell));
        }
        // todo 线程条调用时
        try {
            row.createCell(this.titleCell.length).getCellStyle().cloneStyleFrom(leftCellStyle);
        } catch (IndexOutOfBoundsException e) {
            throw new IndexOutOfBoundsException("线程条调用时异常：" + e.getCause());
        }
    }

    /**
     * 创建行row
     *
     * @param sheet  表
     * @param rowNum 行号
     * @return 行
     */
    public static Row createRow(Sheet sheet, int rowNum) {
        Row row = sheet.getRow(rowNum);

        if (row == null) {
            row = sheet.createRow(rowNum);
        }

        return row;
    }

    /**
     * 创建单元格cell
     *
     * @param row     行
     * @param cellNum 单元格索引
     * @return 单元格
     */
    public static Cell createCell(Row row, int cellNum) {
        Cell cell = row.getCell(cellNum);

        if (cell == null) {
            cell = row.createCell(cellNum);
        }

        return cell;
    }

    /**
     * 单元格数据类型判断
     *
     * @param cell 单元格
     * @return 值
     */
    private String getCellValue(Cell cell) {
        String cellValue = "";
        DecimalFormat df = new DecimalFormat("#");
        switch (cell.getCellTypeEnum()) {
            case STRING:
                cellValue = cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:
                cellValue = df.format(cell.getNumericCellValue()).toString();
                break;
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    private void clearRepeat(File file) {
        System.out.println("去重");
    }

    @Override
    public <T> List<T> readExcel() {
        return null;
    }

    class ExcelThread implements Runnable {// 任务接口
        FileInfoModel returnFile;

        public FileInfoModel getReturnFile() {
            return returnFile;
        }

        ExcelThread(FileInfoModel fileInfoModel) {
            this.returnFile = fileInfoModel;
        }

        @Override
        public void run() {
            long beginTime = System.currentTimeMillis();
            this.returnFile = split(this.returnFile);
            System.out.println(MessageFormat.format("分割文件{0},执行耗时{1}秒", this.returnFile.getForIndex(), (System.currentTimeMillis() - beginTime) / 1000f));
        }
    }
}

